from sqlalchemy import Column, String, Text, DateTime, Index, Table, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.core.database import Base


class Character(Base):
    __tablename__ = "characters"
    __table_args__ = (
        # MySQL 全文索引（针对名称与描述字段）
        Index('ix_characters_fulltext', 'name', 'name_en', 'description', mysql_prefix='FULLTEXT'),
        {
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci'
        }
    )

    id = Column(String(100), primary_key=True, index=True)
    name = Column(String(100), nullable=False, index=True)
    name_en = Column(String(100), nullable=False)
    avatar = Column(String(500), nullable=True)
    description = Column(Text, nullable=True)
    background = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    # 关系映射（规范化，多对多）
    languages_rel = relationship(
        "Language",
        secondary="character_languages",
        back_populates="characters",
        lazy="joined"
    )
    personalities_rel = relationship(
        "Personality",
        secondary="character_personalities",
        back_populates="characters",
        lazy="joined"
    )
    skills_rel = relationship(
        "Skill",
        secondary="character_skills",
        back_populates="characters",
        lazy="joined"
    )
    
    def to_dict(self):
        """转换为字典格式"""
        # 使用规范化关联数据
        languages = [lang.name for lang in getattr(self, "languages_rel", [])]
        personalities = [p.name for p in getattr(self, "personalities_rel", [])]
        skills_list = [
            {"id": str(skill.id), "name": skill.name, "description": skill.description or ""}
            for skill in getattr(self, "skills_rel", [])
        ]

        return {
            "id": self.id,
            "name": self.name,
            "nameEn": self.name_en,
            "avatar": self.avatar,
            "description": self.description,
            "background": self.background,
            "language": languages,
            "personality": personalities,
            "skills": skills_list
        }
